package com.svt.database;

import java.util.ArrayList;
import java.util.List;

import com.stv.objects.Category;
import com.stv.objects.Icon;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;

public class DataBase {
	public static final String DATABASE_NAME = "STV";
	private static final int DATABASE_VERSION = 1;

	public static final String TABLE_CATEGORY = "category";
	public static final String FIELD_CATEGORY_ID = "category_id";
	public static final String FIELD_CATEGORY_NAME = "category_name";
	public static final String FIELD_CATEGORY_ICONS = "category_icons";

	public static final String TABLE_ICON = "icon";
	public static final String FIELD_ICON_ID = "icon_id";
	public static final String FIELD_ICON_REFERENCE = "icon_reference";
	public static final String FIELD_ICON_CATEGORY_ID = "icon_category_id";

	private Context context;

	private SQLiteDatabase db;

	private SQLiteStatement insertCategoryStmt;
	private SQLiteStatement insertIconStmt;

	private static final String INSERT_CATEGORY = "insert into "
			+ TABLE_CATEGORY + " ("
			+ FIELD_CATEGORY_ID + ","
			+ FIELD_CATEGORY_NAME + ","
			+ FIELD_CATEGORY_ICONS
			+ ") values (?,?,?) ";

	private static final String INSERT_ICON = "insert into "
			+ TABLE_ICON + " ("
			+ FIELD_ICON_ID + ","
			+ FIELD_ICON_REFERENCE + ","
			+ FIELD_ICON_CATEGORY_ID
			+ ") values (?,?,?) ";

	public DataBase(Context context) {
		this.context = context;
		OpenHelper openHelper = new OpenHelper(this.context);
		this.db = openHelper.getWritableDatabase();

		this.insertCategoryStmt = this.db.compileStatement(INSERT_CATEGORY);
		this.insertIconStmt = this.db.compileStatement(INSERT_ICON);

	}

	public long insertCategory(Category c) {
		this.insertCategoryStmt.bindLong(1, c.getId());
		this.insertCategoryStmt.bindString(2, c.getName());
		this.insertCategoryStmt.bindString(3, /* c.getIcons() */"");
		return this.insertCategoryStmt.executeInsert();
	}

	public long insertIcon(Icon ic) {
		this.insertIconStmt.bindLong(1, ic.getId());
		this.insertIconStmt.bindString(2, ic.getReference());
		this.insertIconStmt.bindLong(3, ic.getCategoryId());
		return this.insertIconStmt.executeInsert();
	}

	// public long updateTrack(GpxFileDB gpx) {
	//
	// ContentValues cv = new ContentValues();
	// cv.put(FIELD_VELOCITY, gpx.getVelocity());
	// cv.put(FIELD_PACE, gpx.getPace());
	// cv.put(FIELD_TIME, gpx.getTime());
	// cv.put(FIELD_DISTANCE, gpx.getDistance());
	// cv.put(FIELD_ALTITUDE, gpx.getAltitude());
	// cv.put(FIELD_TOWN, gpx.getTown());
	// return this.db.update(TABLE1_NAME, cv, FIELD_ID + "= ?", new String[]
	// {String.valueOf(gpx.getId())});
	// }

	public void close() {
		if (db != null) {
			this.db.close();
		}
	}

	public void deleteAllCategories() {
		this.db.delete(TABLE_CATEGORY, null, null);
	}

	public void deleteOneCategory(String id) {
		this.db.delete(TABLE_CATEGORY, DataBase.FIELD_CATEGORY_ID + " = ? ", new String[] { id });
	}

	public List<Category> selectAllCategories() {

		List<Category> list = new ArrayList<Category>();

		Cursor cursor = this.db.query(TABLE_CATEGORY, new String[] {
				FIELD_CATEGORY_ID, FIELD_CATEGORY_NAME, FIELD_CATEGORY_ICONS },
				null, null, null, null, FIELD_CATEGORY_ID + " asc");

		if (cursor.moveToFirst()) {
			do {

				list.add(new Category(cursor.getInt(0), cursor.getString(1), /* cursor.getString(2) */ null));

			} while (cursor.moveToNext());
		}
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
		}

		return list;
	}

	public Category selectCategoryById(String id) {

		Category c = null;
		Cursor cursor = this.db.query(TABLE_CATEGORY, new String[] {
				FIELD_CATEGORY_ID, FIELD_CATEGORY_NAME, FIELD_CATEGORY_ICONS },
				DataBase.FIELD_CATEGORY_ID + " = ? ", new String[] { id },
				null, null, FIELD_CATEGORY_NAME + " asc");
		if (cursor.moveToFirst()) {
			c = new Category(cursor.getInt(0), cursor.getString(1), /* cursor.getString(2) */ null);
			cursor.close();
			return c;
		}
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
		}
		return c;
	}

	// public List<Icon> selectAllIconsFromCategory(long id) {
	//
	// List<Icon> list = new ArrayList<Icon>();
	//
	//
	// return list;
	// }

	public void deleteAllIcons() {
		this.db.delete(TABLE_ICON, null, null);
	}

	public void deleteOneIcon(String id) {
		this.db.delete(TABLE_ICON, DataBase.FIELD_ICON_ID + " = ? ", new String[] { id });
	}

	public List<Icon> selectAllIcons() {

		List<Icon> list = new ArrayList<Icon>();

		Cursor cursor = this.db.query(TABLE_ICON, new String[] { FIELD_ICON_ID,
				FIELD_ICON_REFERENCE, FIELD_ICON_CATEGORY_ID }, null, null,
				null, null, FIELD_ICON_ID + " asc");

		if (cursor.moveToFirst()) {
			do {

				list.add(new Icon(cursor.getLong(0), cursor.getString(1), cursor.getLong(2)));

			} while (cursor.moveToNext());
		}
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
		}

		return list;
	}

	public Icon selectIconById(String id) {

		Icon ic = null;
		Cursor cursor = this.db.query(TABLE_ICON, new String[] { FIELD_ICON_ID,
				FIELD_ICON_REFERENCE, FIELD_ICON_CATEGORY_ID },
				DataBase.FIELD_ICON_ID + " = ? ", new String[] { id }, null,
				null, FIELD_ICON_CATEGORY_ID + " asc");
		if (cursor.moveToFirst()) {
			ic = new Icon(cursor.getLong(0), cursor.getString(1), cursor.getLong(2));
			cursor.close();
			return ic;
		}
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
		}
		return ic;
	}

	private static class OpenHelper extends SQLiteOpenHelper {

		OpenHelper(Context context) {
			super(context, DataBase.DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {

			db.execSQL("CREATE TABLE " + TABLE_CATEGORY + " ("
					+ FIELD_CATEGORY_ID + " LONG NOT NULL,  "
					+ FIELD_CATEGORY_NAME + " TEXT, "
					+/**/ FIELD_CATEGORY_ICONS + " TEXT " + " )");

			db.execSQL("CREATE TABLE " + TABLE_ICON + " ("
					+ FIELD_ICON_ID + " LONG NOT NULL,  "
					+ FIELD_ICON_REFERENCE + " TEXT, "
					+ FIELD_ICON_CATEGORY_ID + " LONG " + " )");

		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			// Log.v("Upgrading database, this will drop tables and recreate.");

			db.execSQL("DROP TABLE IF EXISTS " + TABLE_CATEGORY);
			db.execSQL("DROP TABLE IF EXISTS " + TABLE_ICON);

			onCreate(db);
		}
	}

}
